{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "ename": "",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m运行具有“Python 3.7.3”的单元格需要ipykernel包。\n",
      "\u001b[1;31m运行以下命令，将 \"ipykernel\" 安装到 Python 环境中。\n",
      "\u001b[1;31m命令: \"/usr/bin/python3 -m pip install ipykernel -U --user --force-reinstall\""
     ]
    }
   ],
   "source": [
    "import os\n",
    "import shutil\n",
    "\n",
    "from openpyxl import load_workbook\n",
    "\n",
    "split_file = 'aa.xlsx'\n",
    "\n",
    "target_dir = 'dist'\n",
    "if os.path.exists(target_dir):\n",
    "    shutil.rmtree(target_dir)\n",
    "os.mkdir(target_dir)\n",
    "\n",
    "from_wb = load_workbook(split_file)\n",
    "\n",
    "nums = range(1, 21)\n",
    "\n",
    "all_row_start = 2\n",
    "all_row_end = 1000\n",
    "\n",
    "CLASS_COL = 1\n",
    "SEX_COL = 3\n",
    "\n",
    "\n",
    "def del_ws_rows(ws, to_del_rows):\n",
    "    to_del_rows.reverse()\n",
    "    for r in to_del_rows:\n",
    "        ws.delete_rows(r, 1)\n",
    "\n",
    "\n",
    "for c in nums:\n",
    "    print(c, 'ban')\n",
    "    one_file = f'{target_dir}/noprint{c}班-全部.xlsx'\n",
    "    from_wb.save(one_file)\n",
    "\n",
    "    new_wb = load_workbook(one_file)\n",
    "    new_ws = new_wb.active\n",
    "    not_current_classes = []\n",
    "    i = 2\n",
    "    for l in new_ws.iter_rows(min_row=3):\n",
    "        i += 1\n",
    "        if l[CLASS_COL].value != c:\n",
    "            not_current_classes.append(i)\n",
    "\n",
    "    del_ws_rows(new_ws, not_current_classes)\n",
    "\n",
    "    # before_range = (all_row_start, to_keep[0]-all_row_start)\n",
    "    # after_range = (to_keep[-1]+1, all_row_end-to_keep[-1])\n",
    "\n",
    "    # print(before_range, after_range)\n",
    "\n",
    "    # if after_range[1] > 0:\n",
    "    #     new_ws.delete_rows(after_range[0], after_range[1])\n",
    "    # if before_range[1] > 0:\n",
    "    #     new_ws.delete_rows(before_range[0], before_range[1])\n",
    "\n",
    "    new_ws.delete_cols(CLASS_COL+1)\n",
    "    new_ws.cell(1,1).value = f'{c}班'\n",
    "    new_wb.save(one_file)\n",
    "    print('分班完成')\n",
    "\n",
    "    # 整男生\n",
    "    male_wb = load_workbook(one_file)\n",
    "    male_ws = male_wb.active\n",
    "\n",
    "    male_list = []\n",
    "    female_list = []\n",
    "    j = 2\n",
    "    for l in male_ws.iter_rows(min_row=3):\n",
    "        j += 1\n",
    "        if l[SEX_COL].value == '男':\n",
    "            male_list.append(j)\n",
    "        else:\n",
    "            female_list.append(j)\n",
    "\n",
    "    print(f'{c}班男生人数{len(male_list)}女生人数{len(female_list)}')\n",
    "\n",
    "    male_file = f'{target_dir}/{c}班-男生.xlsx'\n",
    "    female_file = f'{target_dir}/{c}班-女生.xlsx'\n",
    "\n",
    "    del_ws_rows(male_ws, female_list)\n",
    "\n",
    "    # female_list.reverse()\n",
    "    # for m in female_list:\n",
    "    #     # print('da', m)\n",
    "    #     male_ws.delete_rows(m, 1)\n",
    "    male_ws.delete_cols(11)\n",
    "    male_ws.delete_cols(9)\n",
    "    ind = 1\n",
    "    for r in male_ws.iter_rows(min_row=3):\n",
    "        r[0].value = ind\n",
    "        r[1].value = f'{c}班'\n",
    "        ind += 1\n",
    "\n",
    "    male_wb.save(male_file)\n",
    "\n",
    "    female_wb = load_workbook(one_file)\n",
    "    female_ws = female_wb.active\n",
    "\n",
    "    del_ws_rows(female_ws, male_list)\n",
    "\n",
    "    # male_list.reverse()\n",
    "    # for f in male_list:\n",
    "    #     # print('da', m)\n",
    "    #     female_ws.delete_rows(f, 1)\n",
    "\n",
    "    female_ws.delete_cols(10)\n",
    "    female_ws.delete_cols(8)\n",
    "\n",
    "    ind = 1\n",
    "    for r in female_ws.iter_rows(min_row=3):\n",
    "        r[0].value = ind\n",
    "        r[1].value = f'{c}班'\n",
    "        ind += 1\n",
    "    female_wb.save(female_file)\n",
    "\n",
    "    print(male_list, female_list)\n"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "ff4f4f027397639f154445b85350881a992ca2ec5bd65851114482a9ed1b9108"
  },
  "kernelspec": {
   "display_name": "Python 3.9.0 64-bit (system)",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
